﻿using Dapper;
using Entity;
using IRepository;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Repository
{
    public class NumberingRuleRepository : INumberingRuleRepository, IDisposable
    {
        private readonly IDbConnection _dbConnection;
        private bool _disposed = false;

        public NumberingRuleRepository(IConfiguration configuration)
        {
            string connectionString = configuration.GetConnectionString("DefaultConnection");
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentException("数据库连接字符串 'DefaultConnection' 未配置");
            }
            _dbConnection = new SqlConnection(connectionString);
            _dbConnection.Open();
        }

        /// <summary>
        /// 根据ID获取编号规则
        /// </summary>
        public NumberingRule GetById(int ruleId)
        {
            if (ruleId <= 0)
                throw new ArgumentOutOfRangeException(nameof(ruleId), "规则ID必须大于0");

            // 关联查询目标表单名称（使用TargetForm字段）
            string sql = @"SELECT nr.*, tf.TargetForm
                          FROM wms_Numbering_Rules nr
                          JOIN TargetFormTable tf ON nr.TargetID = tf.TargetID
                          WHERE nr.RuleID = @RuleID AND nr.IsDeleted = 0";

            try
            {
                return _dbConnection.QueryFirstOrDefault<NumberingRule>(sql, new { RuleID = ruleId });
            }
            catch (SqlException ex)
            {
                throw new Exception($"查询ID为{ruleId}的编号规则失败", ex);
            }
        }

        /// <summary>
        /// 保存编号规则（新增/更新）
        /// </summary>
        public int SaveRule(NumberingRule rule)
        {
            if (rule == null)
                throw new ArgumentNullException(nameof(rule));

            ValidateRule(rule);

            try
            {
                if (rule.RuleID == 0)
                {
                    // 新增规则
                    string sql = @"INSERT INTO wms_Numbering_Rules 
                                  (TargetID, NumberPrefix, IncludeYear, IncludeMonth, IncludeDay,
                                   IncludeHour, IncludeMinute, IncludeSecond, SerialNumber,
                                   Step, NumberProductionRule, Operator, OperationTime,
                                   Creator, CreationTime, IsDeleted)
                                  VALUES 
                                  (@TargetID, @NumberPrefix, @IncludeYear, @IncludeMonth, @IncludeDay,
                                   @IncludeHour, @IncludeMinute, @IncludeSecond, @SerialNumber,
                                   @Step, @NumberProductionRule, @Operator, GETDATE(),
                                   @Creator, GETDATE(), 0);
                                  SELECT SCOPE_IDENTITY();";

                    return _dbConnection.QuerySingle<int>(sql, rule);
                }
                else
                {
                    // 更新规则
                    string sql = @"UPDATE wms_Numbering_Rules 
                                  SET TargetID = @TargetID,
                                      NumberPrefix = @NumberPrefix,
                                      IncludeYear = @IncludeYear,
                                      IncludeMonth = @IncludeMonth,
                                      IncludeDay = @IncludeDay,
                                      IncludeHour = @IncludeHour,
                                      IncludeMinute = @IncludeMinute,
                                      IncludeSecond = @IncludeSecond,
                                      SerialNumber = @SerialNumber,
                                      Step = @Step,
                                      NumberProductionRule = @NumberProductionRule,
                                      Operator = @Operator,
                                      OperationTime = GETDATE(),
                                      Modifier = @Operator,
                                      ModificationTime = GETDATE()
                                  WHERE RuleID = @RuleID
                                  AND IsDeleted = 0";

                    _dbConnection.Execute(sql, rule);
                    return rule.RuleID;
                }
            }
            catch (SqlException ex)
            {
                throw new Exception($"{(rule.RuleID == 0 ? "新增" : "更新")}编号规则失败", ex);
            }
        }

        public int GetMaxSerialNumber(int targetId, string numberPrefix)
        {
            if (targetId <= 0)
                throw new ArgumentException("目标表单ID不能为空", nameof(targetId));

            if (string.IsNullOrWhiteSpace(numberPrefix))
                throw new ArgumentException("编号前缀不能为空", nameof(numberPrefix));

            string sql = @"SELECT ISNULL(MAX(SerialNumber), 0) 
                          FROM wms_Numbering_Rules 
                          WHERE TargetID = @TargetID 
                          AND NumberPrefix = @NumberPrefix 
                          AND IsDeleted = 0";

            try
            {
                return _dbConnection.ExecuteScalar<int>(sql, new
                {
                    TargetID = targetId,
                    NumberPrefix = numberPrefix
                });
            }
            catch (SqlException ex)
            {
                throw new Exception("获取最大流水号失败", ex);
            }
        }

        public int UpdateSerialNumber(int targetId, string numberPrefix, int newSerialNumber)
        {
            if (targetId <= 0)
                throw new ArgumentException("目标表单ID不能为空", nameof(targetId));

            if (string.IsNullOrWhiteSpace(numberPrefix))
                throw new ArgumentException("编号前缀不能为空", nameof(numberPrefix));

            if (newSerialNumber < 0)
                throw new ArgumentOutOfRangeException(nameof(newSerialNumber), "流水号不能为负数");

            string sql = @"UPDATE wms_Numbering_Rules 
                          SET SerialNumber = @NewSerialNumber,
                              ModificationTime = GETDATE(),
                              Modifier = @CurrentUser
                          WHERE TargetID = @TargetID 
                          AND NumberPrefix = @NumberPrefix 
                          AND IsDeleted = 0";

            try
            {
                string currentUser = "system"; // 实际应从认证上下文获取
                return _dbConnection.Execute(sql, new
                {
                    NewSerialNumber = newSerialNumber,
                    CurrentUser = currentUser,
                    TargetID = targetId,
                    NumberPrefix = numberPrefix
                });
            }
            catch (SqlException ex)
            {
                throw new Exception("更新流水号失败", ex);
            }
        }

        public List<NumberingRule> GetByConditions(NumberingRuleQueryParams queryParams)
        {
            var sqlBuilder = new StringBuilder(@"
        SELECT nr.*, tf.TargetForm
        FROM wms_Numbering_Rules nr
        JOIN TargetFormTable tf ON nr.TargetID = tf.TargetID
        WHERE nr.IsDeleted = 0
    ");
            var parameters = new DynamicParameters();

            if (queryParams == null)
            {
                sqlBuilder.Append(" ORDER BY nr.OperationTime DESC");
                return _dbConnection.Query<NumberingRule>(sqlBuilder.ToString()).AsList();
            }

            if (queryParams.TargetID.HasValue)
            {
                sqlBuilder.Append(" AND nr.TargetID = @TargetID");
                parameters.Add("@TargetID", queryParams.TargetID.Value);
            }

            if (!string.IsNullOrWhiteSpace(queryParams.NumberPrefix))
            {
                sqlBuilder.Append(" AND nr.NumberPrefix LIKE @NumberPrefix");
                parameters.Add("@NumberPrefix", $"%{queryParams.NumberPrefix}%");
            }

            if (!string.IsNullOrWhiteSpace(queryParams.TimeRule))
            {
                sqlBuilder.Append(" AND nr.TimeRule LIKE @TimeRule");
                parameters.Add("@TimeRule", $"%{queryParams.TimeRule}%");
            }

            // 处理操作时间查询，将前端传来的时间字符串转为 DateTime 并进行精确匹配
            if (!string.IsNullOrWhiteSpace(queryParams.OperationTime))
            {
                if (DateTime.TryParse(queryParams.OperationTime, out DateTime operationTime))
                {
                    // 精确匹配年月日时分秒
                    sqlBuilder.Append(" AND nr.OperationTime >= @OperationTimeStart AND nr.OperationTime <= @OperationTimeEnd");
                    parameters.Add("@OperationTimeStart", operationTime.Date);
                    parameters.Add("@OperationTimeEnd", operationTime.Date.AddDays(1).AddMilliseconds(-1));
                }
            }

            sqlBuilder.Append(" ORDER BY nr.OperationTime DESC");

            try
            {
                return _dbConnection.Query<NumberingRule>(sqlBuilder.ToString(), parameters).AsList();
            }
            catch (SqlException ex)
            {
                throw new Exception("多条件查询编号规则失败", ex);
            }
        }

        // 验证规则数据
        private void ValidateRule(NumberingRule rule)
        {
            if (rule.TargetID <= 0)
                throw new ArgumentException("目标表单不能为空", nameof(rule.TargetID));

            if (string.IsNullOrWhiteSpace(rule.NumberPrefix))
                throw new ArgumentException("编号前缀不能为空", nameof(rule.NumberPrefix));

            if (rule.SerialNumber <= 0)
                throw new ArgumentException("流水号必须大于0", nameof(rule.SerialNumber));

            if (rule.Step <= 0)
                throw new ArgumentException("步长必须大于0", nameof(rule.Step));

            if (rule.RuleID == 0 && string.IsNullOrWhiteSpace(rule.Creator))
                throw new ArgumentException("创建人不能为空", nameof(rule.Creator));
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    if (_dbConnection != null && _dbConnection.State != ConnectionState.Closed)
                    {
                        _dbConnection.Close();
                        _dbConnection.Dispose();
                    }
                }
                _disposed = true;
            }
        }

        public int GetMaxSerialNumber(string targetForm, string numberPrefix)
        {
            throw new NotImplementedException();
        }

        public int UpdateSerialNumber(string targetForm, string numberPrefix, int newSerialNumber)
        {
            throw new NotImplementedException();
        }

        ~NumberingRuleRepository()
        {
            Dispose(false);
        }
    }
}
